
SELECT
products.name,
DATE_FORMAT(orders.created,"%m"),
SUM(orders.quantity),
COUNT(impressions.id)
FROM shop_product AS products
INNER JOIN shop_orderitem AS orders
ON orders.product_id = products.id
LEFT JOIN shop_impression AS impressions
ON impressions.product_id = orders.product_id
AND
DATE_FORMAT(orders.created,”%Y%m") = DATE_FORMAT(impressions.created,”%Y%m")
GROUP BY
products.name,
DATE_FORMAT(orders.created,"%m")
We select the necessary columns from our
products, orders, and impressions tables as we
did previously using the DATE_FORMAT() function
to segment by month.
We first INNER JOIN on product id to include only
products that have sold. Then LEFT JOIN on
product ID, but also on the same date format.
This ensures that we are counting impressions
and orders that happened in the same month.
Challenge Solution